#!/usr/bin/env python
# -*- coding:utf-8 -*-
# author:BINGO
# datetime:2018/10/30 下午 04:58
# software: PyCharm
import pymysql
from code2.Detail import Detail


# 获得数据库连接
def getConnect():
    return pymysql.connect(
        host="localhost",
        user="root",
        password="sa",
        db="db_xq",
        port=3306,
        charset='utf8',
    )


def insertErr(msg, remark, dbConn):
    db = dbConn
    c = db.cursor()
    sql = 'INSERT INTO err ( msg, remark) VALUES ( "%s","%s");' % (msg, remark)
    try:
        i = c.execute(sql)
        print('插入一条错误的数据%s' % i)
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        pass


def findStreetNameByErr(db):
    streetList = []
    c = db.cursor()
    sql = 'SELECT * FROM err'
    try:
        c.execute(sql);
        resList = c.fetchall()
        for res in resList:
            streetName = res[1]
            rList = findStreetIdschoolByStreetName(db, streetName)
            streetList.extend(rList)
    except Exception as e:
        print(e)
    finally:
        return streetList


def delStreetFromErr(detail, db):
    c = db.cursor()
    sql = 'DELETE  FROM err WHERE msg = "%s"' % detail.street
    try:
        c.execute(sql)
    except Exception as e:
        print(e)


def findStreetIdschoolByStreetName(db, streetName):
    streetList = []
    c = db.cursor()
    sql = 'SELECT * FROM street WHERE street = "%s"' % streetName
    try:
        c.execute(sql)
        resList = c.fetchall()
        for res in resList:
            d = Detail()
            d.idschool = res[1]
            d.street = res[2]
            streetList.append(d)
    except Exception as e:
        print(e)
    finally:
        return streetList


def insertDetail(d, dbConn):
    db = dbConn
    cursor = db.cursor()
    sql = 'INSERT INTO detail (idNumber, idschool,time, title, area, street,streetDetail,houseType, acreage, price, unitPrice,url, phone, tag, img1, img2, img3, img4, img5, img6, dataFrom) ' \
          'VALUES (NULL,"%s","%s", "%s", "%s", "%s","%s","%s","%s", "%s","%s","%s", NULL, "%s", "%s", NULL, NULL, NULL, NULL, NULL, "%s");' % (
              d.idschool, d.time, d.title, d.area, d.street, d.streetDetail, d.houseType, d.acreage, d.price,
              d.unitPrice, d.url, d.tag,
              d.img1, d.dataFrom)
    print(sql)
    try:
        i = cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("插入这个一条数据 ===》 %s " % i)
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        # cursor.close()
        pass


def findStreetName():
    db = getConnect()
    sql = 'SELECT * FROM street'
    cursor = db.cursor()
    listDatas = []
    try:
        cursor.execute(sql)
        resList = cursor.fetchall()
        for res in resList:
            d = Detail()
            d.idschool = res[1]
            d.street = res[2]
            listDatas.append(d)
            # print(detail.street)
    except Exception as e:
        print(e)
    finally:
        # cursor.close()
        db.close()
        return listDatas
